<?php
/**
 * <https://y.st./>
 * Copyright © 2019 Alex Yst <mailto:copyright@y.st>
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program. If not, see <https://www.gnu.org./licenses/>.
**/

$xhtml = array(
	'<{title}>' => 'Entity-relationship diagram',
	'<{subtitle}>' => 'Written in <span title="Databases 2">CS 3306</span> by <a href="https://y.st./">Alexand(er|ra) Yst</a>, finalised and <a href="https://y.st./en/coursework/CS3306/Entity-relationship_diagram.xhtml">archived</a> on 2019-04-17',
	'<{copyright year}>' => '2019',
	'takedown' => '2017-11-01',
	'<{body}>' => <<<END
<div class="APA_title_page">
	<p>
		Entity-relationship diagram<br/>
		Alex Yst<br/>
		<span title="Databases 2">CS 3306</span>
	</p>
</div>
<h2>The diagram</h2>
<img src="/img/CC_BY-SA_4.0/y.st./coursework/CS3306/entity-relationship_diagram.png" alt="Entity-relationship diagram" class="framed-centred-image" width="872" height="316"/>
<h2>Flawed design</h2>
<p>
	We have been asked to create an entity-relationship diagram for a car insurance company that involves customers, cars, and accidents.
	Customers are to be related to one or more cars, while cars are to be related to zero or more accidents.
	What I notice right away is that customers are not directly related to accidents, which is quite counter-intuitive.
	Firstly, it means that if a customer sells a car they had an accident in, they&apos;re no longer even indirectly related to the accident.
	It can&apos;t count against them.
	Secondly, it means that if the customer insures a car that had been in an accident before they&apos;d bought the car, the care insurance company might hold that accident against the customer, as they have no way to tie accidents directly to customers and must instead resort to indirect methods of estimating how many accidents a customer has been in when determining that customer&apos;s rates.
	We&apos;re not supposed to fix the flaws in the insurance company&apos;s model though; we&apos;re only supposed to build it as per the instructions provided, so I&apos;ve left that quite-large issue in the design.
</p>
<h2>We need attributes, including identifiers</h2>
<p>
	Next, the format for entity-relationship diagrams specified by the textbook this week requires that we know the attributes of the entities.
	I&apos;ve chosen some attributes I think would be logical, but I don&apos;t run an insurance company, and because I don&apos;t have a car, I don&apos;t have car insurance.
	My assumptions as to what information would be tracked are guesses at best, so your results may be very different.
	The book also tells us that a unique identifier for each entity within an entity set is required.
	At first, I was going to just use a meaningless integer for each entity, but then I realised that two of the entity sets already came with perfect unique identifiers anyway.
	I believe in most countries, a license with a unique license number is required in order to be allowed to drive.
	That serves as a perfect identifier for customers of automotive insurance, and is information the insurance company would want on fine anyway.
	In some countries, such as my own, licenses can be issued by any of several local governments, and aren&apos;t issued on the national level.
	There therefore have numbers unique within the jurisdiction, but not within the entire country.
	For countries run in such a way, the driver license number in the database can be prefixed with some code representing the jurisdiction it was issued in, such as a state or province.
	For example, I live in Oregon, which has a state code of &quot;OR&quot;
	If my driver license number was &quot;0000000&quot;, my driver license in this database would be &quot;OR0000000&quot;, which would be distinct from &quot;CA0000000&quot;, which might be issued to someone in California, just south of me.
	That way, uniqueness in the database is preserved.
	As for motor vehicles, I considered using registration plate numbers as the unique identifiers, but decided instead to use $a[VIN]s.
	Registration plate numbers change in some cases, and can even be transferred between vehicles sometimes.
	$a[VIN]s, on the other hand, are both permanent and unique to each vehicle.
	I still had to use a meaningless integer $a[ID] for accidents though.
	There&apos;s no universal accident number recorded somewhere or something.
</p>
<h3>Customer attributes</h3>
<p>
	For customers, in addition to a driver license number, I figured we need contact information.
	I chose to go with an email address and a postal address.
	Many companies want different (or even more) contact information than that, but I didn&apos;t want to clutter my diagram too much.
	We also need their monthly cost, but decided to have that calculated from information in their car entity set instead.
	That will eliminate odd quirks that might otherwise come up, such as paying for cars that are no longer insured or not paying for cars that are.
	There&apos;s no running total that might get desynchronised.
	We&apos;ll also include billing information.
	What this entails might depend on the insurance company.
	It might include credit card information ans whether the customer has automatic payments enabled or not.
</p>
<h3>Vehicular attributes</h3>
<p>
	As mentioned previously, the vehicle&apos;s $a[VIN] will serve as the unique identifier in the car entity set, not the registration plate number.
	However, that doesn&apos;t mean we don&apos;t want the registration plate number on file too, so we&apos;ll include that in the database as well.
	We&apos;ll also want the car&apos;s model year for determining car age, which may factor in when recalculating the cost of insuring the vehicle, in addition to the number of accidents the car has been in.
	We also need to know how much the car is insured for in case of totalling or theft, and we&apos;ll want to keep track of what the monthly bill is.
	While this is partly calculated from other values, we&apos;re not going to want to calculate it every time we need it and special cases might influence this number as well.
</p>
<h3>Accident attributes</h3>
<p>
	We need a way to uniquely identify accidents, so we&apos;ll use an automatically incrementing integer that starts at zero.
	We&apos;ll call it the accident&apos;s $a[ID], and it&apos;s completely meaningless.
	It seems logical to include what insured car was in the accident, but we&apos;ve got to remember that this information is already available via the relationship this entity has with the relevant car entity.
	On a lower level, we&apos;ll probably need to implement that relationship as including the car&apos;s $a[VIN] in the accident table, but an entity-relationship diagram is on a higher level than that and doesn&apos;t include tables.
	In addition to the $a[ID], we&apos;ll want to record how much the insurance company had to shell out because of this particular accident, just for our records and for adjusting the insurance rate for this car.
</p>
<h2>Conclusion</h2>
<p>
	The entity-relationship diagrams format presented by the textbook in this course seems to be a bit different than the format presented in <a href="https://y.st./en/coursework/CS2203/" title="Databases 1">CS 2203</a>.
	<span title="Databases 1">CS 2203</span>&apos;s idea of entity-relationship diagrams was more compact, putting attributes within the same nodes as the entities they were a part of.
	It also allowed for a way of marking which attributes were the unique ones.
	I think <span title="Databases 1">CS 2203</span>&apos;s method of expressing cardinality is easier to read as well.
	I&apos;m not sure either format is more correct than the other though.
	Both probably have their place, it which you choose probably depends mostly on preference; either yours or that of the person you&apos;re building the diagram to explain the database to.
</p>
END
);
